# -*- coding: utf-8 -*-
import pymongo
client = pymongo.MongoClient('127.0.0.1')
db = client.dmm
import re
import MySQLdb
from zlib import crc32
import MySQLdb.cursors
conn = MySQLdb.connect("127.0.0.1","root","123456789", "shantianjun",charset="utf8mb4",cursorclass=MySQLdb.cursors.DictCursor)
sql = "select * from dmm_play"
cursor = conn.cursor()
cursor.execute(sql)
play = cursor.fetchallDict()
cursor.execute("select * from dmm_series")
series = cursor.fetchallDict()
cursor.execute("select * from dmm_director")
director = cursor.fetchallDict()
cursor.execute("select * from dmm_manufacturer")
manufacturer = cursor.fetchallDict()
cursor.execute("select * from dmm_cate")
cate = cursor.fetchallDict()
data = {}
dataList = []
urltool = set()
tmp = db.girl.find({"date":"2018-02-15"})
for y in tmp:
        if y["url"] in urltool:
            print "contine"
            continue
        data = {}
        # data.fromkeys(['seriesId',
        #  'thumb',
        #  'designation',
        #  'title',
        #  'url',
        #  'series',
        #  'img',
        #  'directorId',
        #  'director',
        #  'bwh',
        #  'version',
        #  'video',
        #  'cate',
        #  'cateId',
        #  'bigImg',
        #  'urlHash',
        #  'apply',
        #  'manufacturerId',
        #  'manufacturer',
        #  'type',
        #  'name'],"empty")
        if not y.get("title"):
            continue
        for yy in series:
            if yy["name"] == y.get("series"):
                data["seriesId"] = yy["id"]
                break
        for yy in director:
            if yy["name"] == y.get("director"):
                data["directorId"] = yy["id"]
                break
        for yy in manufacturer:
            if yy["name"] == y.get("manufacturer"):
                data["manufacturerId"] = yy["id"]
                break
        for yy in cate:
            if yy["name"] == y.get("tag"):
                data["cateId"] = yy["id"]
                break
        data["name"] = y["name"]
        data["bwh"] = y["bwh"]
        data["release"] = y.get("designation")
        data["title"] = y["title"]
        if y.get("bigImg"):
            result = db.img.find_one({"type":"bigImg","source":y.get("bigImg")})
            if result:
                data["bigImg"] = result.get("path")
        else:
            data["bigImg"] = ""
        if y["url"].find('videoa') > 0:
            data['type'] = 1
        elif y["url"].find('videoc') > 0:
            data['type'] = 2
        elif y["url"].find('anime') > 0:
            data['type'] = 3
        elif y["url"].find('nikkatsu') > 0:
            data['type'] = 4
        data["url"] = y["url"].replace("http://www.dmm.co.jp","")
        data["urlHash"] = str(crc32(data["url"])).replace("-","")
        data["series"] = y.get("series")
        data["director"] = y.get("director")
        data["cate"] = y.get("tag")
        data["version"] = y.get("release")
        data["apply"] = y.get("apply")
        data["manufacturer"] = y.get("manufacturer")
        if y.get("thumb"):
            result = db.img.find_one({"type":"thumb","source":y.get("thumb")})
            if result:
                data["thumb"] = result.get("path")
        else:
            data["thumb"] = ""
        if y.get("video"):
            result = db.img.find_one({"type":"video","source":y.get("video")})
            if result:
                data["video"] = result.get("path")
        else:
            data["video"] = ""
        if y.get("imgList"):
            tmp = []
            for yy in y.get("imgList"):
                result = db.img.find_one({"type":"imgList","source":yy})
                if result:
                    tmp.append(result.get("path"))
                data["img"] = ",".join(tmp)
        fields = tuple(data.keys())
        fields = str(tuple(fields)).replace("'",'')
        data_dict = data.values()
        print data
        urltool.add(y["url"])
        # if "data_values" not in dir():
        data_values = "(" + "%s," * (len(data_dict)) + ")"
        data_values = data_values.replace(',)', ')')

        # dataList.append(tuple(data_dict))
        sql = "insert into dmm_av %s values %s" % (fields,data_values)
        cursor.execute(sql,tuple(data_dict))
        print sql

cursor.close()
conn.commit()
# insert info cate (name) select select distinct(cate) from stores where cate <> ""
